/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistencia;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import negocio.Tabela;
import negocio.Coluna;

/**
 *
 * @author asouza
 */
public class DaoExecuteQuery {

    
    Connection conn = null;

    public DaoExecuteQuery(Connection c) {
        this.conn = c;
    }

    

    public List<Tabela> getTabelas(String o) throws Exception {
        try {
            List<Tabela> aux = new LinkedList<>();
            Statement sta = this.conn.createStatement();
            String query = "SELECT tabela FROM BB102936.OWNER o, TABLE(o.owner_tables) ot WHERE o.owner_name = '" + o + "'";
            ResultSet res = sta.executeQuery(query);
            while (res.next()) {
                aux.add(new Tabela(res.getString("tabela"),o));
            }
            res.close();
            sta.close();
            return aux;
        } catch (Exception ex) {
            throw new Exception("DaoExecuteQuery >> getTabelas\n" + ex.getMessage());
        }
    }

    public List<Coluna> getColunas(String owner, String table) throws Exception {
        try {
            List<Coluna> aux = new LinkedList<>();
            Coluna qualquer = null;
            Statement sta = this.conn.createStatement();
            String query = "SELECT   o.owner_name, ot.tabela, ot.retencao_dados, "+
            "		 ot.linhas_iniciais, ot.taxa_crescimento,         			 "+
            "		 col.coluna, col.tipo, col.tamanho, col.precisao, 			 "+
            "		 col.is_null nulo, col.percentual_nulo, col.valor_medio      "+   
            " FROM 																 "+ 
            "	  BB102936.owner o,                                    "+
            "	  TABLE (o.owner_tables) ot,                           "+      
            "	  TABLE (ot.colunas) col                               "+
            " WHERE o.owner_name = '" + owner +"'"+    
            "	  AND col.tabela = ot.tabela  "+
            "	  AND ot.tabela = '"+ table +"'";  

            
            ResultSet res = sta.executeQuery(query);

            while (res.next()) {
                qualquer = new Coluna();
                qualquer.setColuna(res.getString("COLUNA"));
                qualquer.setNulo(res.getString("NULO"));
                qualquer.setPrecisao(res.getInt("PRECISAO"));
                qualquer.setTipo(res.getString("TIPO"));
                qualquer.setTabela(res.getString("TABELA"));
                qualquer.setTamanho(res.getInt("TAMANHO"));
                qualquer.setPercentualNulo(res.getFloat("PERCENTUAL_NULO"));
                qualquer.setTamanhoMedio(res.getFloat("VALOR_MEDIO"));
                aux.add(qualquer);
            }
            res.close();
            sta.close();

            return aux;
        } catch (Exception e) {
            throw new Exception("DaoExecuteQuery >> getColunas\n" + e.getMessage());

        }
    }

     public boolean gravarNoDicionario(Tabela d) throws Exception {
        try {
            Statement sta = this.conn.createStatement();
            
            String tca = (d.getTaxaCrescimentoAnual() == 0)? "NULL": ""+d.getTaxaCrescimentoAnual();
            String rd = (d.getReterDados() == 0)? "NULL": ""+d.getReterDados();
            
            String query ="BEGIN UPDATE TABLE (SELECT o.owner_tables " +
                        "    FROM bb102936.owner o,                                  " +  
                        "         TABLE (o.owner_tables) ott               " +
                        "   WHERE o.owner_name        ='"+ d.getOwner()  +"'"+
                        "     AND ott.tabela     ='"+ d.getTabela() +"'"+
                        " ) t                                                   "+
                        " SET t.retencao_dados   ="+ d.getReterDados()           +
                        "    ,t.taxa_crescimento ="+ tca +
                        "    ,t.linhas_iniciais  ="+ d.getLinhaEstimadoIncial()  +
                        " WHERE t.tabela         ='"+ d.getTabela()+"';COMMIT;END;" ;
            
            if (sta.executeUpdate(query) > 0) {
                for(Coluna t: d.getListaDeColunas())
                {
                    String pn = (t.getPercentualNulo() == 0)? "NULL" : ""+t.getPercentualNulo();
                    String tm = (t.getTamanhoMedio() == 0)? "NULL" : ""+t.getTamanhoMedio();
                    
                    query ="BEGIN UPDATE TABLE (SELECT ott.colunas     "+
                    "FROM bb102936.owner o,                                "+
                      "     TABLE (o.owner_tables) ott             "+
                    " WHERE o.owner_name = '"+ d.getOwner()+ "'"+ 
                    " AND ott.tabela = '"+d.getTabela()+"') c SET " +
                    " c.percentual_nulo = "+ pn    +                   
                    " ,c.valor_medio =    "+ tm       +
                    " WHERE c.tabela ='"+d.getTabela()+"' and c.coluna = '"+t.getColuna()+"'; COMMIT;END;";
                    sta.executeUpdate(query);
                }    
                sta.close();
                return true;
            }
        } catch (Exception e) {
            throw new Exception("DaoExecuteQuery >> gravarNoDicionario\n" + e.getMessage());

        }
        return false;
    }

    public Tabela recuperarDoDicionario(String owner, String table) throws Exception {
        try {
            Tabela d = null;
             
            ResultSet resVlr = this.conn.createStatement().executeQuery(
            " SELECT  distinct                      "+
            "   SUM(nvl(ot.vlr_bruto,0)) vlr_bruto  "+
            " FROM 	  BB102936.owner o,         "+ 
            "   TABLE(o.owner_tables) ot 	    "+
            " WHERE o.owner_name = '"+owner.toUpperCase()+"'");
            
            double vrlBruto =0;
            if(resVlr.next()) vrlBruto = resVlr.getDouble("vlr_bruto");
            Statement sta = this.conn.createStatement();
            String query ="SELECT rownum linha, o.owner_name, ot.tabela, ot.retencao_dados, ot.taxa_crescimento, "+
            "	   ot.linhas_iniciais, ot.tablespace_name, ot.INI_LIQ, ot.INI_BRT, ot.PROX_LIQ, ot.PROX_BRT, "+
            "	   col.coluna,col.tipo, col.tamanho, col.precisao, col.is_null, "+
            "	   col.percentual_nulo,  col.valor_medio, ot.vlr_bruto "+
            " FROM "+
            "	  BB102936.owner o, "+
            "	  TABLE(o.owner_tables) ot, "+ 
            "	  TABLE(ot.colunas) col   "+
            " WHERE o.owner_name = '"+ owner.toUpperCase() + "'" +
            "    AND col.tabela = ot.tabela "+        
            "	  AND ot.tabela = '"+ table.toUpperCase() +"'";

       
       
            ResultSet res = sta.executeQuery(query);
            d = new Tabela(table.toUpperCase(), owner.toUpperCase());
            List<Coluna> tabela = new LinkedList();
            
            while (res.next()) {
                
                if(res.getInt("linha") == 1)
                {
                    d.setReterDados(res.getInt("retencao_dados"));
                    d.setColunaTamanhoMedio(res.getFloat("valor_medio"));
                    d.setColunaValorNulo(res.getFloat("percentual_nulo"));
                    d.setTaxaCrescimentoAnual(res.getFloat("taxa_crescimento"));
                    d.setLinhaEstimadoIncial(res.getInt("linhas_iniciais"));
                    d.setInicialBruto(res.getDouble("INI_BRT"));
                    d.setInicialLiquido(res.getDouble("INI_LIQ"));
                    d.setNextLiquido(res.getDouble("PROX_LIQ"));
                    d.setNextBruto(res.getDouble("PROX_BRT"));
                    d.setTablespace(res.getString("tablespace_name"));
                    d.setValorBruto(vrlBruto);
                }
                
                //colunas
                Coluna tab = new Coluna();
                tab.setTabela(res.getString("tabela"));
                tab.setColuna(res.getString("coluna"));
                tab.setTipo(res.getString("tipo"));
                tab.setTamanho(res.getInt("tamanho"));
             
                tab.setNulo(res.getString("is_null"));
                tab.setTamanhoMedio(res.getFloat("valor_medio"));
                tab.setPercentualNulo(res.getFloat("percentual_nulo"));
                tab.setPrecisao(res.getInt("precisao"));
                tabela.add(tab);
                
            }
            d.setListDeColunas(tabela);
            return d;
        } catch (Exception e) {
            throw new Exception("DaoExecuteQuery >> recuperarDoDicionario\n" + e.getMessage());
        }

    }
    
  public List<String> getListOwners() throws Exception {
        try {
            List<String> aux = new LinkedList<>();
            Statement sta = this.conn.createStatement();
            String query = "SELECT OWNER_NAME FROM BB102936.OWNER";

            ResultSet res = sta.executeQuery(query);

            while (res.next()) {
                aux.add(res.getString("OWNER_NAME"));
            }
            res.close();
            sta.close();
            return aux;
        } catch (Exception e) {
            throw new Exception("DaoExecuteQuery >> getListOwners\n" + e.getMessage());
        }
    }

    
    public void calculoVolumetrico(String owner) throws Exception
    {
        Statement sta = this.conn.createStatement();
        String stmt = "DECLARE t BB102936.t_owner;  " +
                " BEGIN " +
                "   SELECT VALUE(o) into t from BB102936.owner o WHERE o.owner_name ='"+owner+"';" +
                "   t.volumetric_calculation();                            "+ 
                " EXCEPTION                                                "+
                "   WHEN OTHERS THEN                                       "+
                "   BEGIN                                                  "+
                "     ROLLBACK;                                            "+
                "     raise_application_error(-20102, 'INSERT T_OWNER');   "+
                "   END;                                                   "+
                " END;";  
        ResultSet res = sta.executeQuery(stmt);
    }
    
      public boolean populaOwner() throws SQLException
      {
        String stmt ="DECLARE                                                   "+
            "  vTypeTables  BB102936.NT_TABLES;                                 "+                       
            "  vTypeColumns BB102936.NT_COLUMNS;                                "+
            "BEGIN                                                              "+
            "   BEGIN                                                           "+
            "        execute immediate 'DELETE FROM BB102936.OWNER';            "+
            "		 COMMIT;                                                "+
            "     EXCEPTION                                                     "+
            "      WHEN OTHERS THEN                                             "+
            "        raise_application_error(-20101, 'TRUNCATE TABLE OWNER ');  "+
            "      END;                                                         "+
            "   FOR vcOwner IN (SELECT distinct owner FROM all_tables)          "+
            "   LOOP                                                            "+
            "       vTypeTables  := BB102936.NT_TABLES();                       "+
            "       vTypeColumns := BB102936.NT_COLUMNS();                      "+
            "       FOR vcTable IN (SELECT table_name, owner, pct_free,         "+
            "                         tablespace_name, num_rows FROM all_tables "+
            "                               where owner = vcOwner.owner)        "+ 
            "       LOOP                                                        "+
            "           FOR vcColumns IN (SELECT                                "+
            "                                c.column_name coluna, c.data_type tipo,  c.data_length tamanho,                "+
            "                                c.data_precision precisao, c.data_scale, c.nullable eh_nulo, t.tablespace_name "+
            "                            FROM all_tables t,                     "+
            "                                 all_tab_columns c                 "+
            "                            WHERE                                  "+
            "                                 t.table_name = c.table_name       "+
            "                                 AND c.owner = t.owner             "+
            "                                 AND t.tablespace_name = vcTable.tablespace_name "+
            "                                 AND t.owner = vcTable.owner                     "+
            "                                 AND t.table_name = vcTable.table_name)          "+
            "             LOOP                                                  "+
            "                  vTypeColumns.EXTEND;                             "+
            "                  vTypeColumns (vTypeColumns.LAST) :=              "+
            "                      BB102936.t_columns (                         "+
            "                                  vcTable.OWNER,                   "+
            "                                  vcTable.table_name,              "+
            "                                  vcColumns.coluna,                "+
            "                                  vcColumns.tipo,                  "+
            "                                  vcColumns.tamanho,               "+
            "                                  vcColumns.precisao,              "+
            "                                  vcColumns.eh_nulo,               "+
            "                                  NULL,                            "+  
            "                                  NULL                             "+
            "                                 );                                "+
            "               END LOOP;                                           "+
            "               vTypeTables.EXTEND;                                 "+ 
            "              vTypeTables (vTypeTables.LAST) := BB102936.t_tables ("+
            "                               vcTable.table_name,                 "+
            "                               vcTable.OWNER,                      "+
            "                               NULL,                               "+
            "                               NULL,                               "+
            "                               vcTable.num_rows,                   "+
            "                               vcTable.Tablespace_name,            "+
            "                               NULL,                               "+
            "                               NULL,                               "+
            "                               NULL,                               "+
            "                               NULL,                               "+
            "                               vcTable.pct_free,                   "+ 
            "                               NULL,                               "+     
            "                               vTypeColumns);                      "+
            "       END LOOP;                                                   "+
            "      BEGIN                                                        "+ 
            "            INSERT INTO BB102936.OWNER VALUES(vcOwner.OWNER, vTypeTables);  "+
            "            vTypeColumns := NULL;                                  "+
            "            vTypeTables  := NULL;                                  "+
            "            COMMIT;                                                "+
            "           EXCEPTION                                               "+
            "            WHEN OTHERS THEN                                       "+
            "            BEGIN                                                  "+
            "              ROLLBACK;                                            "+
            "              raise_application_error(-20102, 'INSERT T_OWNER');   "+
            "            END;                                                   "+
            "       END;                                                        "+
            "   END LOOP;                                                       "+
            "END;";
         // System.out.println(stmt);
        return this.conn.createStatement().execute(stmt);
      }

}
